
  -- EXEC sp_yield_assembly 'Assembly', '0', '0','29,30', '', '','XLREPORT', '0'
  ALTER proc sp_yield_assembly
  (
	@PROCESS_TYPE		VARCHAR(50),
	@PROCESSSTEP_IDS	VARCHAR(100),
	@BASEPRODUCT_ID		VARCHAR(50),
	@VENDOR			    VARCHAR(100),
	@FROMDATE		    VARCHAR(50),
	@TODATE 		    VARCHAR(50),
	@REPORT_TYPE		VARCHAR(50),
	@DEFAULT_PROMPT		VARCHAR(50)
)

 AS
 BEGIN
				
	CREATE TABLE #RESULT_TABLE ( [BASE PRODUCT] VARCHAR(50), [PART NUMBER] VARCHAR(50), [Vendor] VARCHAR(50),[PROCESS TYPE] VARCHAR(20),[LOT NUMBER] VARCHAR(50),[FAB LOT NUMBER] VARCHAR(50),
						[Assy In Date] DATETIME, [Assy Out Date] DATETIME, [Assy In Qty] INT, [Assy Out Qty] INT,[Yield] FLOAT,
						[Lot Type] VARCHAR(50),[Date Code] VARCHAR(50), [Ship To Vendor] VARCHAR(100) )
  	
	DECLARE @SQLSTRING	NVARCHAR(4000)
	DECLARE @SQLSTRING1	NVARCHAR(4000)
	DECLARE @EXCELEXECUTESTRING	NVARCHAR(4000)
	DECLARE @CRYSTALEXECUTESTRING	NVARCHAR(4000)
	SET @SQLSTRING = N' INSERT INTO #result_table
						SELECT (SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID =
					CASE
						WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID
						WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID
					END
				) AS BASE_PRODUCT,
				P.PART_NUMBER, L_VENDOR_NAME, PS_TYPE, LOT_NUMBER,FAB_LOT_NUMBER,
				ISNULL((SELECT MIN(DATE_IN) FROM LOT_TABLE_HISTORY WHERE LOT_ID = LT.LOT_ID ), LT.DATE_IN ) AS DATE_IN ,
				(SELECT MAX(SHIP_DATE) FROM SHIPPED_LOT WHERE LOT_ID = LT.LOT_ID) AS DATE_OUT ,
				ISNULL((SELECT MAX(QTY_IN) QTY_IN FROM LOT_TABLE_HISTORY WHERE LOT_ID = LT.LOT_ID ), LT.QTY_IN ) AS QTY_IN,
				(SELECT SUM(SHIP_QTY) FROM SHIPPED_LOT WHERE LOT_ID = LT.LOT_ID) AS QTY_OUT, 
				ROUND(CAST((SELECT SUM(SHIP_QTY) FROM SHIPPED_LOT WHERE LOT_ID = LT.LOT_ID) AS FLOAT) * 100 / CAST(ISNULL( (SELECT MAX( QTY_IN ) FROM LOT_TABLE_HISTORY WHERE LOT_ID = LT.LOT_ID ), LT.QTY_IN ) AS FLOAT), 2)  AS YIELD,
				 lot_category, LT.LOT_DATE_CODE, SL.SL_NextLocation
				FROM PRODUCT P, PROCESS_STEP PS, LOCATION L, LOT_TABLE LT, SHIPPED_LOT SL
				WHERE P.PROCESSSTEP_ID=PS.PROCESSSTEP_ID AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
				AND LT.LOCATION_ID = L.LOCATION_ID AND LT.LOT_ID = SL.LOT_ID AND lot_current_status = ''close'' '
	
	SET @SQLSTRING1 = ''
	
	IF ( @PROCESS_TYPE IS NOT NULL )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND PS.PS_TYPE = ''' +  @PROCESS_TYPE + '''' 
	END

	IF ( @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND LT.PROCESSSTEP_ID = ''' +  @PROCESSSTEP_IDS + '''' 
	END 

	IF ( @BASEPRODUCT_ID IS NOT NULL AND @BASEPRODUCT_ID <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND P_PARENT_PRODUCT_ID =  ''' +  @BASEPRODUCT_ID + '''' 
	END
	
	IF ( @VENDOR IS NOT NULL AND @VENDOR <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' and L.VENDOR_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING (  ''' + @VENDOR + '''' +') )'
	END

	/*IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND DATE_IN >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00 AM' +''''
	END
	
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND LT.DATE_OUT <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59 PM' +''''
	END */

	--PRINT @SQLSTRING + @SQLSTRING1

	EXEC (@SQLSTRING + @SQLSTRING1)
	
	IF @REPORT_TYPE = 'XLREPORT'
	BEGIN
			SET @EXCELEXECUTESTRING = 'select 
			[PART NUMBER] As [DEVICE],[VENDOR],[LOT NUMBER] As [Subcon Lot No], [FAB LOT NUMBER] as [Wafer Lot No], [FAB LOT NUMBER] As [Ikanons Lot No],[Lot Type],
			[Date Code], [Assy In Date], [Assy Out Date],[Assy In Qty],[Assy Out Qty], [Yield], [Ship To Vendor]
			from #result_table'

			IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
			BEGIN
				SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' WHERE  [Assy In Date] >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00 AM' +''''
			END
	
			IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
			BEGIN
				SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' AND [Assy Out Date] <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59 PM' +''''
			END
			SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' order by [FAB LOT NUMBER], [Assy In Date] desc '

			EXEC (@EXCELEXECUTESTRING)
	END
	ELSE
	BEGIN
		SET @CRYSTALEXECUTESTRING = ' select * from #result_table '

		IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
		BEGIN
			SET @CRYSTALEXECUTESTRING = @CRYSTALEXECUTESTRING + ' WHERE  [Assy In Date] >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00 AM' +''''
		END

		IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
		BEGIN
			SET @CRYSTALEXECUTESTRING = @CRYSTALEXECUTESTRING + ' AND [Assy Out Date] <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59 PM' +''''
		END

		SET @CRYSTALEXECUTESTRING = @CRYSTALEXECUTESTRING + ' order by [FAB LOT NUMBER], [Assy In Date] desc '
		
		EXEC (@CRYSTALEXECUTESTRING)
	END

END


